package com.snail.common.toolUtil.jdbc;

import com.snail.common.util.StringUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtil {

    public static boolean execute(String driver, String url, String name, String pwd, String sql) throws Exception {
        Connection conn = getConnection(driver, url, name, pwd);
        PreparedStatement pstmt = conn.prepareStatement(sql);
        boolean rs = pstmt.execute();

        closeStatement(pstmt);
        closeConnection(conn);
        return rs;
    }

    public static Integer executeUpdate(String driver, String url, String name, String pwd, String sql) throws Exception {
        Connection conn = getConnection(driver, url, name, pwd);
        PreparedStatement pstmt = conn.prepareStatement(sql);
        int result = pstmt.executeUpdate();
        closeStatement(pstmt);
        closeConnection(conn);
        return result;
    }

    public static ResultSet executeQuery(String driver, String url, String name, String pwd, String sql) throws Exception {
        Connection conn = getConnection(driver, url, name, pwd);
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet result = pstmt.executeQuery();
        closeStatement(pstmt);
        closeConnection(conn);
        return result;
    }

    /***
     * 执行查询sql并返回结果集
     * @param driver 驱动类
     * @param url jdbc-url
     * @param account 账号
     * @param pwd 密码
     * @param sql
     */
    public static Map<String, Object> queryMap(String driver, String url, String account, String pwd, String sql) throws Exception {
        Map<String, Object> result = new HashMap<>();
        Connection conn = getConnection(driver, url, account, pwd);
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        List<Map<String, Object>> list = toMapOfList(rs);
        if (list.size() > 0) {
            result = list.get(0);
        }
        colseResource(conn, pstmt, rs);
        return result;
    }

    /***
     * 执行查询sql并返回结果集
     * @param driver 驱动类
     * @param url jdbc-url
     * @param account 账号
     * @param pwd 密码
     * @param sql
     */
    public static List<Map<String, Object>> queryListMap(String driver, String url, String account, String pwd, String sql) throws Exception{
        Connection conn = getConnection(driver, url, account, pwd);
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        List<Map<String, Object>> result = toMapOfList(rs);
        colseResource(conn, pstmt, rs);
        return result;
    }

    public static Connection getConnection(String driver, String url, String name, String pwd) throws Exception{
        Connection conn = null;
        Class.forName(driver);    //加载数据可驱动
        conn = DriverManager.getConnection(url, name, pwd);
        return conn;
    }


    /*
     * 把得到的一列数据存入到一个对象中
     */
    public static List<Object> queryListString(String driver, String url, String account, String pwd, String sql) throws Exception {
        List<Object> result = new ArrayList();
        Connection conn = getConnection(driver, url, account, pwd);
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        //获取结果集元数据(获取此 ResultSet 对象的列的编号、类型和属性。)
        ResultSetMetaData rd = rs.getMetaData();
        if (rd.getColumnCount() == 1) {
            while (rs.next()) {
                result.add(rs.getObject(1));
            }
        }
        colseResource(conn, pstmt, rs);
        return result;
    }

    /* 把得到的一列数据存入到一个对象中
     * @param rs
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws SQLException
     * @throws NoSuchMethodException
     * @throws SecurityException
     * @throws IllegalArgumentException
     * @throws InvocationTargetException
     */
    private static List<Map<String, Object>> toMapOfList(ResultSet rs) throws SQLException, SecurityException, IllegalArgumentException {
        List<Map<String, Object>> result = new ArrayList<>();
        //获取结果集元数据(获取此 ResultSet 对象的列的编号、类型和属性。)
        ResultSetMetaData rd = rs.getMetaData();
        int columnCount = rd.getColumnCount();
        while (rs.next()) {
            Map<String, Object> map = new HashMap<>();
            for (int i = 0; i < columnCount; i++) {
                //获取列名
                String columnName = rd.getColumnLabel(i + 1);
//                System.out.print(columnName+"("+rs.getObject(columnName)+")("+rd.getColumnType(i + 1)+")");
                //获取列类型
                int columnType = rd.getColumnType(i + 1);
                switch (columnType) {
                    case Types.NULL:
                        break;
                    case Types.NVARCHAR:    //-9
                    case Types.VARCHAR: //12
                    case Types.CHAR:    //1
                        if (!StringUtil.isEmpty(rs.getString(columnName))) {
                            map.put(columnName.toLowerCase(), rs.getString(columnName).trim());
                        }
                        break;
                    case Types.INTEGER: //4
                    case Types.SMALLINT:    //5
                        map.put(columnName.toLowerCase(), rs.getInt(columnName));
                        break;
                    case Types.BIGINT:
                        map.put(columnName.toLowerCase(), rs.getLong(columnName));
                        break;
                    case Types.DATE:
                    case Types.TIMESTAMP:
                        try {
                            map.put(columnName.toLowerCase(), rs.getTimestamp(columnName));
                        } catch (Exception e) {
                            map.put(columnName.toLowerCase(), rs.getString(columnName));
                        }
                        break;
                    case Types.DECIMAL: //3
                        map.put(columnName.toLowerCase(), rs.getBigDecimal(columnName));
                        break;
                    case Types.DOUBLE:
                    case Types.NUMERIC:
                        map.put(columnName.toLowerCase(), rs.getDouble(columnName));
                        break;
                    case Types.BIT:
                        map.put(columnName.toLowerCase(), rs.getBoolean(columnName));
                        break;
                    default:
                        map.put(columnName.toLowerCase(), rs.getObject(columnName).toString());
                        break;
                }
            }
//            System.out.println();
            result.add(map);
        }
        return result;
    }

    /**
     * 释放资源
     */
    public static void colseResource(Connection conn, Statement st, ResultSet rs) {
        closeResultSet(rs);
        closeStatement(st);
        closeConnection(conn);
    }

    /**
     * 释放连接 Connection
     */
    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //等待垃圾回收
        conn = null;
    }

    /**
     * 释放语句执行者 Statement
     */
    public static void closeStatement(Statement st) {
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //等待垃圾回收
        st = null;
    }

    /**
     * 释放结果集 ResultSet
     */
    public static void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //等待垃圾回收
        rs = null;
    }


    public static void main(String[] args) throws Exception {
//        List<Map<String, Object>> result = queryListMap("oracle.jdbc.driver.OracleDriver",
//                "jdbc:oracle:thin:@101.201.118.116:1521:hd40", "hd40", "hd40",
//                "select * from v$version;");
//        System.out.println(JSON.toJSONString(result));
		Connection conn = getConnection("oracle.jdbc.driver.OracleDriver",
                "jdbc:oracle:thin:@101.201.118.116:1521:HDAPPCS", "hd40", "hd40");
		System.out.println(conn);
    }
}
